import serial
import serial.tools.list_ports
import re
import pymysql
import time
import datetime


def connectSql():
    conn = pymysql.connect(host='182.92.76.124'  # 连接名称，默认127.0.0.1
                           , user='root'  # 用户名
                           , passwd='zhaozhao'  # 密码
                           , port=3308  # 端口，默认为3306
                           , db='SmartLight'  # 数据库名称
                           , charset='utf8'  # 字符编码
                           )
    return conn


def runSql(cur, sql):
    cur.execute(sql)  # 执行SQL语句


def initSer():
    portx = "COM10"
    # 波特率，标准值之一：50,75,110,134,150,200,300,600,1200,1800,2400,4800,9600,19200,38400,57600,115200
    bps = 115200
    # 超时设置,None：永远等待操作，0为立即返回请求结果，其他值为等待超时时间(单位为秒）
    timex = None
    # 打开串口，并得到串口对象
    ser = serial.Serial(portx, bps, timeout=timex)
    return ser


ser = initSer()
# 连接数据库
conn = connectSql()
cur = conn.cursor()
flag = 0
listArr = []
while True:
    msg = ser.readline()
    # s = msg.encode('unicode_escape').decode('utf-8').replace('\\x','%')
    # un = parse.unquote(s)
    res = msg.decode('gbk', 'replace')
    print(res)
    #  获取串口信息
    if flag == 0 and res[0] == '@':
        listArr.append(re.findall(r"X(.+?)Y", res)[0])
        flag += 1
    if res[1] >= '1' and res[1] <= '2' and res[0] == '!':
        listArr.append(re.findall(r"X(.+?)Y", res)[0])
        flag += 1
    if (flag == 3):
        timeNow = datetime.datetime.now().strftime("%Y-%m-%d %H:%M:%S")
        # sql = """insert into gps (lamp_id,longitude,latitude,get_time) values (%s,%s,%s,%s)"""
        
        #  关于gps信息更新的操作
        gps_update_sql = """update gps set longitude=%s,latitude=%s,get_time=%s where lamp_id=%s"""
        gps_new_values = (listArr[1], listArr[2], timeNow, '1')
        print('gps_new_values')
        print(gps_new_values)
        cur.execute(gps_update_sql, gps_new_values)
        
        #  关于报警信息插入的操作
        alarm_insert_sql = """insert into alarm(lamp_id,alarm_state,alarm_time) values (%s,%s,%s)"""
        alarm_new_values = ('1', listArr[0], timeNow)
        print('alarm_new_values')
        print(alarm_new_values)
        cur.execute(alarm_insert_sql, alarm_new_values)

        conn.commit()
        listArr = []
        flag = 0
        time.sleep(1)
